﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace SF.Base.Core {
	/// <summary>
	/// 单词操作类
	/// </summary>
	public class DictRules {
		private static Dictionary<string, long> StrToNum = new Dictionary<string, long>();
		private static Dictionary<long, string> NumToStr = new Dictionary<long, string>();

		public static string GetStrId(int did) {
			if (!NumToStr.ContainsKey(did)) {
				DictEntity Dict = GetDictByDidFromCache(did);
				if (Dict != null) {
					NumToStr.Add(did, Dict.DictId);
				}
				else {
					return "";
				}
			}
			return NumToStr[did];
		}

		public static long GetNumId(string dictId) {
			if (!StrToNum.ContainsKey(dictId)) {
				DictEntity Dict = GetDictByDictIdFromCache(dictId);
				if (Dict != null) {
					StrToNum.Add(dictId, Dict.Did);
				}
				else {
					return 0;
				}
			}

			return StrToNum[dictId];
		}
		/// <summary>
		/// 比较单词解释
		/// </summary>
		/// <param name="ph1"></param>
		/// <param name="ph2"></param>
		/// <returns></returns>
		public static Boolean CompareDictParaphrase(string ph1, string ph2) {
			if (ph1 == ph2) return true;
			ph1 = Regex.Replace(ph1, @"(abbr\.|prep\.|pron\.|n\.|v\.|conj\.|vi\.|vt\.|aux\.v\.|modal v\.|art\.|num\.|int\.|\[U\]|\[C\]|adj\.|a\.|adv\.|ad\.|\[U,C\]|im\.)", "");
			ph2 = Regex.Replace(ph2, @"(abbr\.|prep\.|pron\.|n\.|v\.|conj\.|vi\.|vt\.|aux\.v\.|modal v\.|art\.|num\.|int\.|\[U\]|\[C\]|adj\.|a\.|adv\.|ad\.|\[U,C\]|im\.)", "");

			ph1 = ph1.Replace(",", ",");
			ph1 = ph1.Replace(";", ",");
			ph1 = ph1.Replace("\r", "");
			ph1 = ph1.Replace("\n", "");
			ph1 = ph1.Replace("(", ",");
			ph1 = ph1.Replace(")", ",");
			ph1 = ph1.Trim();

			ph2 = ph2.Replace(",", ",");
			ph2 = ph2.Replace(";", ",");
			ph2 = ph2.Replace("\r", "");
			ph2 = ph2.Replace("\n", "");
			ph2 = ph2.Replace("(", ",");
			ph2 = ph2.Replace(")", ",");
			ph2 = ph2.Trim();

			string[] aph1 = Regex.Split(ph1, ",");
			string[] aph2 = Regex.Split(ph2, ",");

			for (int i = 0; i < aph1.Length; i++) {
				for (int j = 0; j < aph2.Length; j++) {
					if (aph1[i].Trim() != "" && aph1[i].Trim() == aph2[j].Trim()) {
						return true;
					}
				}
			}
			return false;
		}

		public static DictEntity GetDict(long did) {
			DictEntity Dict = null;
			string SQL = @"select 
							did,dict_id,initial,aspell,apronunciation,asyllable,
							espell,epronunciation,esyllable,frequency,paraphrase,commentate,[status]
							from dt_dicts
							where did = @did
						";
			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						SQL,
						Sh.GetParameter("@did", DbType.Int64, did)
					)
				) {
					if (dr.Read()) {
						Dict = BuildEntity.BuildDictEntity(dr);
					}
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询单词失败", e);
			}
			return Dict;
		}

		public static List<DictEntity> GetRelationDictsByDictId(long did) {
			List<DictEntity> lists = new List<DictEntity>();
			string SQL = @"	select 
								did,dict_id,initial,aspell,apronunciation,asyllable,
								espell,epronunciation,esyllable,frequency,paraphrase,commentate,a.[status],
								tid = isnull((
									select 
										top 1 tid
									from dt_term_taxonomy c
									where c.taxonomy_id = b.taxonomy_id
								),0),
								name = isnull((
									select 
										top 1 name
									from dt_term_taxonomy d
									where d.taxonomy_id = b.taxonomy_id
								),0)
							from dt_dicts a
								inner join dt_term_relationships b
									on a.dict_id = b.object_id
										and b.status = 1
							where dict_id in (
								select 
									dict_id 
								from dt_dicts
								where aspell = (
									select 
										aspell
									from dt_dicts
									where did = @did
								)
								and did != @did
							)
						";
			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						SQL,
						Sh.GetParameter("@did", DbType.Int64, did)
					)
				) {
					while (dataReader.Read()) {

						DictEntity item = BuildEntity.BuildDictEntity(dataReader);

						item.Vid = Sh.GetLong(dataReader, "tid");
						item.VocabularyName = Sh.GetStr(dataReader, "name");

						lists.Add(item);
					}
					Logger.Detail("GetDicts", lists.Count.ToString());
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询相关单词列表失败", e);
			}
			return lists;
		}

		/// <summary>
		/// 更新单词解释
		/// </summary>
		/// <param name="did"></param>
		/// <param name="paraphrase"></param>
		public static int UpdateParaphrase(DictEntity dict, string paraphrase, string commentate, string userId) {
			long Did = dict.Did;
			using (SqlConnection conn = new SqlConnection(BaseSettings.Instance.ConnSF)) {
				conn.Open();
				SqlTransaction trans = conn.BeginTransaction();

				try {

					int c1 = Sh.ExecuteNonQuery(
						trans,
						CommandType.Text,
						"update dt_dicts set paraphrase = @paraphrase,commentate = @commentate where did = @did",
						Sh.GetParameter("@paraphrase", DbType.String, paraphrase),
						Sh.GetParameter("@commentate", DbType.String, commentate),
						Sh.GetParameter("@did", DbType.Int32, Did)
					);
					if (paraphrase != dict.Paraphrase) {
						//备份这个单词的解释
						if (c1 == 1 && !string.IsNullOrEmpty(dict.Paraphrase)) {
							int c2 = Sh.ExecuteNonQuery(
								trans,
								CommandType.Text,
								@"
								insert into dt_edit_paraphrase_log ( dict_id,user_id,paraphrase,edit_date ) 
								values( @dict_id,@user_id,@paraphrase,getdate())
							",
								Sh.GetParameter("@paraphrase", DbType.String, dict.Paraphrase),
								Sh.GetParameter("@user_id", DbType.String, userId),
								Sh.GetParameter("@dict_id", DbType.String, dict.DictId)
							);
						}
					}

					trans.Commit();

					//更新缓存中数据

					List<DictEntity> lists = GetDictsFromCache();

					foreach (DictEntity dt in lists) {
						if (dt.Did == Did) {
							dt.Paraphrase = paraphrase;
							dt.Commentate = commentate;
						}
					}

					CH.SetListCache<DictEntity>(CH.Keys.DICT, lists);

					return 0;
				}
				catch (Exception ex) {
					Logger.Exception(ex, "更新单词解释失败");
					trans.Rollback();
				}
			}
			return -1;
		}

		/// <summary>
		/// 根据拼写查询单词对象
		/// 缓存方式查询
		/// </summary>
		/// <param name="spell">单词拼写</param>
		/// <returns></returns>
		public static DictEntity GetDictBySpellFromCache(string spell) {
			List<DictEntity> lists = GetDictsFromCache();

			//区分大小写查询
			DictEntity dt = lists.Find(delegate(DictEntity t) {
				return string.Equals(t.ASpell, spell, StringComparison.OrdinalIgnoreCase);
			}
			);

			if (dt == null) {
				//不区分大小写查询
				dt = lists.Find(delegate(DictEntity t) { return string.Equals(t.ASpell, spell); });
			}

			return dt;
		}

		/// <summary>
		/// 根据数字ID查询单词对象
		/// 缓存方式查询
		/// </summary>
		/// <returns></returns>
		public static DictEntity GetDictByDidFromCache(long did) {
			List<DictEntity> lists = GetDictsFromCache();

			DictEntity dt = lists.Find(delegate(DictEntity t) { return t.Did == did; });

			return dt;
		}

		/// <summary>
		/// 根据文本ID查询单词对象
		/// 缓存方式查询
		/// </summary>
		/// <returns></returns>
		public static DictEntity GetDictByDictIdFromCache(string DictId) {
			List<DictEntity> lists = GetDictsFromCache();

			DictEntity dt = lists.Find(delegate(DictEntity t) { return t.DictId == DictId; });

			return dt;
		}

		/// <summary>
		/// 查询全部单词列表
		/// 缓存方式查询 
		/// </summary>
		/// <returns></returns>
		public static List<DictEntity> GetDictsFromCache() {
			List<DictEntity> lists = CH.GetListCache<DictEntity>(CH.Keys.DICT);

			if (lists == null) {
				lists = GetDicts();
				CH.SetListCache<DictEntity>(CH.Keys.DICT, lists);
			}
			return lists;
		}

		/// <summary>
		/// 查询全部单词列表
		/// </summary>
		/// <returns>单词对象LIST</returns>
		public static List<DictEntity> GetDicts() {
			List<DictEntity> lists = new List<DictEntity>();
			string SQL = @"select 
							did,dict_id,initial,aspell,apronunciation,asyllable,
							espell,epronunciation,esyllable,frequency,paraphrase,commentate,[status]
							from dt_dicts
						";
			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						SQL
					)
				) {
					while (dataReader.Read()) {

						DictEntity item = BuildEntity.BuildDictEntity(dataReader);

						lists.Add(item);
					}
					Logger.Detail("GetDicts", lists.Count.ToString());
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询单词列表", e);
			}
			return lists;
		}

		/// <summary>
		/// 查询词汇表下的全部单词
		/// </summary>
		/// <param name="vocabularyId"></param>
		/// <returns></returns>
		public static List<DictEntity> GetDictsByVid(string vocabularyId) {

			List<DictEntity> lists = new List<DictEntity>();
			string SQL = @"select 
							did,dict_id,initial,aspell,apronunciation,asyllable,
							espell,epronunciation,esyllable,frequency,paraphrase,commentate,c.[status],
							isdelete = isnull((
								select 
									case status when 1 then 0 else 1 end
								from dt_term_relationships d
								where c.dict_id = d.object_id
								and d.taxonomy_id = @taxonomy_id
							),0)
							from dt_dicts c inner join dt_term_relationships d
							on c.dict_id = d.object_id
							where dict_id in (
								select 
									object_id
								from dt_term_taxonomy a 
									inner join dt_term_relationships b
										on a.taxonomy_id = b.taxonomy_id
								where a.taxonomy_id = @taxonomy_id
									and taxonomy = 'vocabulary'
							)
							order by d.relation_order
						";
			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						SQL,
						Sh.GetParameter("@taxonomy_id", DbType.String, vocabularyId)
					)
				) {
					while (dataReader.Read()) {

						DictEntity item = BuildEntity.BuildDictEntity(dataReader);
						item.IsDelete = Sh.GetInt(dataReader, "isdelete");
						lists.Add(item);
					}
					Logger.Detail("GetDicts", lists.Count.ToString(), vocabularyId);
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询单词列表", e);
			}
			return lists;

		}

		/// <summary>
		/// 查询猜功能至少需要的单词
		/// </summary>
		/// <param name="vocabularyId"></param>
		/// <param name="initial"></param>
		/// <returns></returns>
		public static List<DictEntity> GetGuessDictsByVid(string vocabularyId, string initial) {
			string Sql = @"select top 10
							did,dict_id,initial,aspell,apronunciation,asyllable,
							espell,epronunciation,esyllable,frequency,paraphrase,commentate,[status]
							from dt_dicts
							where parent is not null
							and paraphrase is not null
							order by newid()
						";
			List<DictEntity> lists = new List<DictEntity>();

			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						Sql,
						Sh.GetParameter("@taxonomy_id", DbType.String, vocabularyId)
					)
				) {
					while (dataReader.Read()) {

						DictEntity item = BuildEntity.BuildDictEntity(dataReader);

						lists.Add(item);
					}
					Logger.Detail("GetDicts", lists.Count.ToString());
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询单词列表", e);
			}
			return lists;
		}

		/// <summary>
		/// 查询某词汇表下某首字母开头的单词
		/// </summary>
		/// <param name="vocabularyId">词汇表文本ID</param>
		/// <param name="initial">首字母</param>
		/// <returns></returns>
		public static List<DictEntity> GetDictsByVid(string vocabularyId, string initial) {

			List<DictEntity> lists = new List<DictEntity>();
			string SQL = @"select 
							did,dict_id,initial,aspell,apronunciation,asyllable,
							espell,epronunciation,esyllable,frequency,paraphrase,commentate,c.[status]
							from dt_dicts c inner join dt_term_relationships d
							on c.dict_id = d.object_id
							where initial = @initial
							and dict_id in (
								select 
									object_id
								from dt_term_taxonomy a 
									inner join dt_term_relationships b
										on a.taxonomy_id = b.taxonomy_id
								where a.taxonomy_id = @taxonomy_id
									and taxonomy = 'vocabulary'
							)
							order by d.relation_order
						";
			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						SQL,
						Sh.GetParameter("@initial", DbType.String, initial),
						Sh.GetParameter("@taxonomy_id", DbType.String, vocabularyId)
					)
				) {
					while (dataReader.Read()) {

						DictEntity item = BuildEntity.BuildDictEntity(dataReader);

						lists.Add(item);
					}
					Logger.Detail("GetDicts", lists.Count.ToString());
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询单词列表", e);
			}
			return lists;

		}

		/// 获取用户收藏单词的列表
		/// </summary>
		/// <param name="userid">用户文本ID</param>
		/// <returns></returns>
		public static string[] GetUserCollectDict(string userid) {
			ArrayList al = new ArrayList();
			List<TaxonomyEntity> Taxonomys = TaxonomyRules.GetRelationShipsByTaxonomy("collect-dict", userid);

			foreach (TaxonomyEntity Taxonomy in Taxonomys) {
				al.Add(Taxonomy.ObjectId);
			}

			return (string[])al.ToArray("".GetType());
		}

		/// <summary>
		/// 用户收藏某个单词
		/// </summary>
		/// <param name="UserId">用户文本ID</param>
		/// <param name="DictId">单词的文本ID</param>
		/// <returns>错误码</returns>
		public static int Collect(string userId, string dictId) {

			try {
				int ret = Sh.ExecuteNonQuery(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@"
						declare @guid uniqueidentifier
						select @guid = taxonomy_id from dt_term_taxonomy  where user_id = @user_id and taxonomy = 'collect-dict';
						if (@guid is null)
						begin
							
							set @guid = newid()
							insert into dbo.dt_term_taxonomy (
								taxonomy,[name],[slug],[description],taxonomy_id,[user_id],parent,taxonomy_order,taxonomy_date,[status]
							)
							values(
								'collect-dict','收藏单词',null,'收藏单词',@guid,@user_id,null,0,getdate(),1
							)
							
						end

						if not exists ( select 1 from dt_term_relationships where object_id = @object_id and taxonomy_id = @guid )
						begin
							insert into dt_term_relationships (
								[object_id],taxonomy_id,relation_order,relation_date
							)
							values(
								@object_id,@guid,0,getdate()
							) 
						end
						else
						begin
							delete dt_term_relationships where object_id = @object_id and taxonomy_id = @guid
						end
						
					",
					Sh.GetParameter("@user_id", DbType.String, userId),
					Sh.GetParameter("@object_id", DbType.String, dictId)
				);
				return 0;
			}
			catch (System.Exception e) {
				Logger.Exception("用户关注词汇表失败", e);
				return -160101;
			}

		}

		/// <summary>
		/// 判断用户是否关注此词汇表
		/// </summary>
		/// <param name="userId"></param>
		/// <param name="vocabularyId"></param>
		/// <returns></returns>
		public static Boolean IsCollect(string userId, string vocabularyId) {

			try {
				object o = Sh.ExecuteScalar(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@"
						select 
							trid
						from dt_term_taxonomy a inner join dt_term_relationships b
						on a.taxonomy_id = b.taxonomy_id
						where taxonomy = 'collect-dict'
							and b.object_id = @object_id
							and a.user_id = @user_id
					",
					Sh.GetParameter("@user_id", DbType.String, userId),
					Sh.GetParameter("@object_id", DbType.String, vocabularyId)
				);
				if (o == null) {
					return false;
				}
				return true;
			}
			catch (System.Exception e) {
				Logger.Exception("判断用户是否关注此词汇表失败", e);
				return false;
			}
		}

		/// <summary>
		/// 用户认识某个单词
		/// </summary>
		/// <param name="UserId">用户文本ID</param>
		/// <param name="DictId">单词的文本ID</param>
		/// <returns>错误码</returns>
		public static int Know(string userId, string dictId) {

			try {
				int ret = Sh.ExecuteNonQuery(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@"
						declare @guid uniqueidentifier
						select @guid = taxonomy_id from dt_term_taxonomy  where user_id = @user_id and taxonomy = 'know-dict';
						if (@guid is null)
						begin
							
							set @guid = newid()
							insert into dbo.dt_term_taxonomy (
								taxonomy,[name],[slug],[description],taxonomy_id,[user_id],parent,taxonomy_order,taxonomy_date,[status]
							)
							values(
								'know-dict','知道单词',null,'知道单词',@guid,@user_id,null,0,getdate(),1
							)
							
						end

						if not exists ( select 1 from dt_term_relationships where object_id = @object_id and taxonomy_id = @guid )
						begin
							insert into dt_term_relationships (
								[object_id],taxonomy_id,relation_order,relation_date
							)
							values(
								@object_id,@guid,0,getdate()
							) 
						end
						else
						begin
							delete dt_term_relationships where object_id = @object_id and taxonomy_id = @guid
						end
						
					",
					Sh.GetParameter("@user_id", DbType.String, userId),
					Sh.GetParameter("@object_id", DbType.String, dictId)
				);
				return 0;
			}
			catch (System.Exception e) {
				Logger.Exception("用户认识某个单词失败", e);
				return -160101;
			}
		}

		/// 获取用户收藏单词的列表
		/// </summary>
		/// <param name="userid">用户文本ID</param>
		/// <returns></returns>
		public static string[] GetUserKnowDict(string userId) {
			ArrayList al = new ArrayList();
			List<TaxonomyEntity> Taxonomys = TaxonomyRules.GetRelationShipsByTaxonomy("know-dict", userId);

			foreach (TaxonomyEntity Taxonomy in Taxonomys) {
				al.Add(Taxonomy.ObjectId);
			}

			return (string[])al.ToArray("".GetType());
		}

		public static string[] GetUserKnowDict(string userId, string vocabularyId, string initial) {
			ArrayList al = new ArrayList();

			List<TaxonomyEntity> taxonomys = new List<TaxonomyEntity>();

			string Sql = @"
							select 
								b.object_id
							from dt_term_taxonomy a 
								inner join dt_term_relationships b
									on a.taxonomy_id = b.taxonomy_id
							where a.taxonomy = 'know-dict'
								and a.user_id = @user_id
								and b.object_id in (
									select
										d.object_id
									from dt_term_taxonomy c 
										inner join dt_term_relationships d
											on c.taxonomy_id = d.taxonomy_id
									where c.taxonomy_id = @taxonomy_id
									and c.taxonomy = 'vocabulary'
								)
						";
			if (!string.IsNullOrEmpty(initial)) {
				Sql = @"
							select 
								b.object_id
							from dt_term_taxonomy a 
								inner join dt_term_relationships b
									on a.taxonomy_id = b.taxonomy_id
							where a.taxonomy = 'know-dict'
								and a.user_id = @user_id
								and b.object_id in (
									select
										d.object_id
									from dt_term_taxonomy c 
										inner join dt_term_relationships d
											on c.taxonomy_id = d.taxonomy_id
									where c.taxonomy_id = @taxonomy_id
									and c.taxonomy = 'vocabulary'
									and d.object_id in (
										select 
											dict_id
										from dt_dicts
										where initial = @initial
									)
								)
						";
			}

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						Sql,
						Sh.GetParameter("@user_id", DbType.String, userId),
						Sh.GetParameter("@initial", DbType.String, initial),
						Sh.GetParameter("@taxonomy_id", DbType.String, vocabularyId)
					)
				) {
					while (dr.Read()) {
						al.Add(dr.GetStr("object_id"));
					}
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询分类关系失败", e);
			}

			return (string[])al.ToArray("".GetType());
		}
	}
}
